package edu.ccut.saturn.component.loader;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import edu.ccut.saturn.component.DatabaseManager;
import edu.ccut.saturn.component.IDataDictTypeInfo;
import edu.ccut.saturn.component.IDictItemInfo;
import edu.ccut.saturn.component.ILogger;
import edu.ccut.saturn.component.SaturnComponentException;
import edu.ccut.saturn.component.SaturnDictionaryManager;
import edu.ccut.saturn.component.SaturnLoggerManager;
import edu.ccut.saturn.component.impl.SaturnDataDictTypeInfo;
import edu.ccut.saturn.component.impl.SaturnDictItemInfo;

public class DictionaryLoader {

	private static final String LOGGER_KEY_LOAD_BUNDLE = SaturnLoggerManager.LOGGER_KEY_LOAD_BUNDLE;

	private static final String QUERY_DICT_TYPE_SQL = "select type,name from saturn_dict_type";

	private static final String QUERY_DICT_SQL = "select type, code, value, pinyin,parent_code  from saturn_dict where type = ?";

	protected static ILogger logger = SaturnLoggerManager.getInstance()
			.getSaturnLogger(LOGGER_KEY_LOAD_BUNDLE);

	private static final String KEY_DICT_TYPE = "type";

	private static final String KEY_DICT_TYPE_NAME = "name";

	private static final String KEY_DICT_CODE = "code";

	private static final String KEY_DICT_VALUE = "value";

	private static final String KEY_DICT_PINYIN = "pinyin";

	private static final String KEY_DICT_PARENT_CODE = "parent_code";
	
	public static void loadDataDictTypeInfo() {

		logger.debug("START Load DICT from DATABASE");

		loadDataDictFromDB();

		logger.debug("END Load DICT from DATABASE");

	}

	private static void loadDataDictFromDB() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;

		try {
			connection = DatabaseManager.getInstance().getConnection();
			if (connection != null) {
				preparedStatement = connection
						.prepareStatement(QUERY_DICT_TYPE_SQL);
				rs = preparedStatement.executeQuery();
				while (rs.next()) {
					String type = rs.getString(KEY_DICT_TYPE);
					String name = rs.getString(KEY_DICT_TYPE_NAME);
					IDataDictTypeInfo dataDictType = loadDataDictTypeInfo(
							type, name);

					SaturnDictionaryManager.getInstance().addBundleDictInfo(
							type, dataDictType);
				}
			}
		} catch (Exception e) {
			logger.error(
					"load dictionary from database failed.",
					e);

		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (rs != null) {
					rs.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	private static IDataDictTypeInfo loadDataDictTypeInfo(String type,
			String name) throws SaturnComponentException {

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;

		try {
			IDataDictTypeInfo dataDictType = new SaturnDataDictTypeInfo();
			dataDictType.setType(type);
			dataDictType.setName(name);
			connection = DatabaseManager.getInstance().getConnection();

			if (connection != null) {

				preparedStatement = connection.prepareStatement(QUERY_DICT_SQL);

				preparedStatement.setString(1, type);

				rs = preparedStatement.executeQuery();
				List<IDictItemInfo> itemList = new ArrayList<IDictItemInfo>();

				while (rs.next()) {
					IDictItemInfo dictItem = new SaturnDictItemInfo();
					dictItem.setType(type);
					dictItem.setCode(rs.getString(KEY_DICT_CODE));
					dictItem.setValue(rs.getString(KEY_DICT_VALUE));
					dictItem.setPinyin(rs.getString(KEY_DICT_PINYIN));
					dictItem.setParentCode(rs.getString(KEY_DICT_PARENT_CODE));
					itemList.add(dictItem);
				}
				if (itemList.size() > 0) {
					dataDictType.setItems(itemList);
				}
			}
			return dataDictType;

		} catch (Exception e) {
			logger.error(
					"load dictionary from database failed.",
					e);

			return null;

		} finally {

			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (rs != null) {
					rs.close();
				}
				if (connection != null) {
					connection.close();
				}

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}
